//
//  DataBaseHandle.m
//  UI SQLite数据库
//
//  Created by dllo on 16/1/11.
//  Copyright (c) 2016年 lanou. All rights reserved.
//

#import "DataBaseHandle.h"
#import <sqlite3.h>
#import "CollectModel.h"
@interface DataBaseHandle()
@property(nonatomic,retain)CollectModel *collect;
@end
@implementation DataBaseHandle
- (void)dealloc
{
    [_collect release];
    [super dealloc];
}

//单例
+(instancetype)shareDataBaseHandle
{
    //在静态区,只初始化一次
    //因为是在静态区,是全局的,所以第二次执行这个方法的时候,会找有没有dataBase,就是DataBaseHandle *指针指向dataBase的是hi偶,指的是不是空地址,因为第一次执行这个方法的时候,因为一开始是空的,所以创建了一个dataBase,对象,
    static DataBaseHandle *dataBase = nil;
    
    if (dataBase == nil) {
        dataBase = [[DataBaseHandle alloc]init];
        
    }
    return dataBase;
}
//定义一个sqlite结构体(对象)
sqlite3 *db;
//打开数据库
-(void)open
{
    //API:sqlite3_open
    if (db!=nil) {
        NSLog(@"数据库已开启");
  
        return;
    
    }
    
    NSString *file = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES).lastObject stringByAppendingPathComponent:@"collect.sqlite"];
    NSLog(@"🐌%@",file);
    
    /**
     *  参数1:数据库文件的路径(类型为C字符串)
     
         参数2:数据库对象
     */
  int result = sqlite3_open(file.UTF8String, &db);
    
    if (result == SQLITE_OK) {
        NSLog(@"打开数据库成功");
    }else{
        NSLog(@"%d",result);
    }
    
}

//创建表格
-(NSInteger)createTable{
    //API:sqlite3_exec
    
    //创建一个SQL语句
    NSString *createTable = @"create table if not exists Collect(number integer primary key autoincrement,code text,name text,calory integer,thumb_image_url text)";
   int result = sqlite3_exec(db, createTable.UTF8String, nil,nil,nil);
    
    
    if (result == SQLITE_OK) {
        NSLog(@"创建表格成功");
    }else{
        NSLog(@"%d",result);
    }
    return result;
}

//插入数据
-(void)insertCollect:(CollectModel *)collect
{
    //API:sqlite3_exec
NSString *insert = [NSString stringWithFormat:@"insert into Collect(code,name,calory,thumb_image_url)values('%@','%@',%ld,'%@')",collect.code,collect.name,collect.calory,collect.thumb_image_url];

    int result = sqlite3_exec(db, insert.UTF8String, nil,nil,nil);
    
    
    if (result == SQLITE_OK) {
        NSLog(@"插入数据成功");
    }else{
        NSLog(@"%d",result);
    }

}

//修改,更新数据

//-(void)updateStudent:(Student *)student number:(NSInteger)number
//{
//    //API:sqlite3_exec
//    NSString *update = [NSString stringWithFormat:@"update lanou1130 set name = '%@',sex = '%@',age = '%ld' where(number = '%ld')",student.name,student.sex,student.age,number];
//    
//    int result = sqlite3_exec(db, update.UTF8String, nil,nil,nil);
//    
//    //result结果为1,是sql语句错
//    if (result == SQLITE_OK) {
//        NSLog(@"刷新数据成功");
//    }else{
//        NSLog(@"%d",result);
//    }
//
//}

//删除数据
-(void)deleteWithCode:(NSString *)code
{
    //API:sqlite3_exec
    NSString *delete = [NSString stringWithFormat:@"delete from Collect where code = '%@'",code];
    int result = sqlite3_exec(db, delete.UTF8String, nil,nil,nil);

    if (result == SQLITE_OK) {
        NSLog(@"除数据成功");
    }else{
        NSLog(@"%d",result);
    }

    
}

-(BOOL)select:(CollectModel *)collect
{
    NSMutableArray *array = [NSMutableArray array];
    
    
   array = [self select];
   // NSLog(@"🍊%@",array);

    BOOL one = NO;
    for (CollectModel *collectOne in array) {
         NSLog(@"🐌%@",collect.code);
        NSLog(@"%@",collectOne.code);
        if ([collectOne.code isEqualToString: collect.code]) {
            one = YES;
            NSLog(@"🍊%d",one);
            break;
        }else{
            one = NO;
        
        }
    }
    return one;
}





//询数据
-(NSMutableArray *)select
{
    //存放符合条件的数据(元素是Model对象)
    NSMutableArray *array = [NSMutableArray array];
    //sql语句
    NSString *select = @"select * from Collect";
    
    //API:stmt结构体,sqlite3_prepare_v2,sqlite3_step,sqlite3_column
    
    //声明一个stmt对象(结构体)
    
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, select.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"准备就绪,查询中...");
        
        //每行都执行查询语句
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            
     //如果能进来到while里面,说明有这条记录,不用再写下面的任何步骤,直接写一个返回值,直接return yes,跳出去,外界用返回值判断有没有这个记录,就是有没有这个要找的东西
            
        /**********************  下面的看需不需要写再写  **********************/
            
            //如果查询条件匹配,通过sqlite3_column函数簇取出值
            //取出哪一列的数据值
          const unsigned char *code = sqlite3_column_text(stmt, 1);
            
            const unsigned char *name = sqlite3_column_text(stmt, 2);
            int calory = sqlite3_column_int(stmt, 3);
            const unsigned char *thumb_image_url = sqlite3_column_text(stmt, 4);

        //创建model对象,赋值后放到数组中
            
            CollectModel *collect = [[CollectModel alloc]init];
            collect.code = [NSString stringWithUTF8String:(const char *)code];
            collect.name = [NSString stringWithUTF8String:(const char *)name];
            collect.calory = calory;
            collect.thumb_image_url = [NSString stringWithUTF8String:(const char *)thumb_image_url];
            
            [array addObject:collect];
        }
        
        
        //销毁对象
        sqlite3_finalize(stmt);
    }else{
        NSLog(@"不能正常查询 %d",result);
    }
    NSLog(@"%@",array);
    return array;
}

//删除表
-(void)dropTable
{
    NSString *drop = @"drop table lanou1130";
    
  int  result = sqlite3_exec(db, drop.UTF8String, nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"除表成功");
    }else{
        NSLog(@"%d",result);
    }
}

//关闭数据库
-(void)closeDB
{
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        NSLog(@"guan数据库成功");
    }else{
        NSLog(@"%d",result);
    }
}



@end
